package cn.restcloud.example.controller.rdb;


import java.sql.ResultSet;
import java.util.List;
import org.bson.Document;
import cn.restcloud.example.model.UserModel;
import cn.restcloud.framework.core.annotation.bean.BeanConfig;
import cn.restcloud.framework.core.annotation.bean.BeanType;
import cn.restcloud.framework.core.annotation.rest.MethodType;
import cn.restcloud.framework.core.annotation.rest.RestConfig;
import cn.restcloud.framework.core.util.DateTimeUtil;
import cn.restcloud.framework.core.util.DocumentUtil;
import cn.restcloud.framework.core.util.PrintUtil;
import cn.restcloud.framework.core.util.db.rdb.RdbUtil;
import cn.restcloud.framework.view.base.DocAndView;
import cn.restcloud.framework.view.util.ViewUtil;

/**
 * RdbUtil工具类使用示例
 * RdbUtil工具类是一个快速访问JDBC数据源的方法封装类
 * @author restcloud
 *
 */

@BeanConfig(appId = "example",beanId = "RdbUtilDemoRest",beanName = "RdbUtil工具类使用示例",beanType = BeanType.CONTROLLER)
@RestConfig(url = "/rest/example/rdbutil")
public class RdbUtilDemoRest {

	@RestConfig(configName = "执行update sql",method = MethodType.GET,url = "/update/sql")
	public DocAndView updateSql() throws Exception{
		String sql="update bpm_maindata set subject='test subject"+DateTimeUtil.getNow()+"' where WF_OrUnid=?";
		int i=RdbUtil.executeUpdateSql(RdbUtil.getConnection("指定数据源id"),sql,"038951960fa5904a530a088074640c6bbcb6"); //传入参数
		return ViewUtil.getSuccessView("update sql 执行结果:"+i);
	}
	
	@RestConfig(configName = "执行delete sql",method = MethodType.GET,url = "/delete/sql")
	public DocAndView deleteSql() throws Exception{
		String sql="delete from bpm_maindata where WF_OrUnid=?";
		PrintUtil.o(RdbUtil.getConnection("default").getAutoCommit());
		int i=RdbUtil.executeUpdateSql(sql,"038951960fa5904a530a088074640c6bbcb6"); //传入参数
		return ViewUtil.getSuccessView("delete sql 执行结果:"+i);
	}
	
	@RestConfig(configName = "执行select sql",method = MethodType.GET,url = "/select/sql/docs")
	public DocAndView selectDocs() throws Exception{
		String sql="select top 2 * from bpm_maindata";
		List<Document> docs=RdbUtil.listDocs(sql);//如果执行其他数据源可传入数据源id,数据源id是在web数据源中配置的
		return ViewUtil.getJsonView(docs);
	}
	
	
	@RestConfig(configName = "执行select sql",method = MethodType.GET,url = "/select/sql/result")
	public DocAndView selectResult() throws Exception{
		String sql="select top 2 * from bpm_maindata";
		ResultSet rs=null;
		String subject="";
		try{
			rs=RdbUtil.executeQuerySql(sql); //如果执行其他数据源可传入数据源id,数据源id是在web数据源中配置的
			while(rs.next()){
				subject+=rs.getString("Subject")+",";
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			RdbUtil.close(rs); //这里需要手动关闭rs对像
		}
		return ViewUtil.getJsonView(subject);
	}
	
	@RestConfig(configName = "保存一个Document对像到表中",method = MethodType.GET,url = "/select/save/doc")
	public DocAndView saveDoc() throws Exception{
		Document doc=new Document();
		doc.put("Subject", "test subject 0012");
		doc.put("WF_OrUnid", DocumentUtil.getNewDocumentId()); //获取一个22位的唯一id号 
		doc.put("WF_Processid", "100001");
		int i=RdbUtil.saveDoc("BPM_MainData", doc, "WF_OrUnid"); //已存在更新，不存在插入
		return ViewUtil.getSuccessView("执行结果:"+i);
	}
	
	@RestConfig(configName = "删除一个Document对像",method = MethodType.GET,url = "/select/delete/doc")
	public DocAndView deleteDoc() throws Exception{
		Document doc=new Document();
		doc.put("Subject", "test subject 001");
		doc.put("WF_OrUnid", "5c4289416d13bc5324877b84");
		doc.put("WF_Processid", "100001");
		int i=RdbUtil.deleteByDoc("BPM_MainData", doc, "WF_OrUnid,Subject"); //根据多个主键字段WF_OrUnid,Subject的值删除doc对像会组成and条件
		return ViewUtil.getSuccessView("执行结果:"+i);
	}
	
	@RestConfig(configName = "根据字段值删除多条记录",method = MethodType.GET,url = "/select/delete/keyids")
	public DocAndView deleteByKeyIds() throws Exception{
		int i=RdbUtil.deleteByKeyIds("BPM_MainData", "5c4289466d13bc5324877b96,5c42895b6d13bc5324877bb5", "WF_OrUnid");
		return ViewUtil.getSuccessView("执行结果:"+i);
	}
	
	@RestConfig(configName = "查询记录并转换为JavaBean对像",method = MethodType.GET,url = "/select/tojavabean")
	public DocAndView selectToJavaBean() throws Exception{
		String sql="select * from BPM_OrgUserList where UserId=?";
//		UserModel userObj=RdbUtil.getBean(UserModel.class, sql, "admin"); //返回单个
		List<UserModel> userObjs=RdbUtil.listBeans(UserModel.class, sql, "admin"); //返回集合
		return ViewUtil.getJsonView(userObjs);
	}
	
	@RestConfig(configName = "保存JavaBean对像到数据库表中",method = MethodType.GET,url = "/select/savejavabean")
	public DocAndView saveJavaBean() throws Exception{
		UserModel userObj=new UserModel();
		userObj.setUserId("test001");
		userObj.setPassword("pass");
		userObj.setJobDesc("经理");
		int i=RdbUtil.saveBean("BPM_OrgUserList", userObj);
		return ViewUtil.getSuccessView("执行结果:"+i);
	}
	
	@RestConfig(configName = "对数据库表进行分页查询",method = MethodType.GET,url = "/list/page")
	public DocAndView listByPage() throws Exception{
		String sql="select * from BPM_OrgUserList";
		int pageNo=1;
		int pageSize=10;
		List<Document> docs=RdbUtil.listDocsByPage(sql, pageNo, pageSize); //根据sql语句使用游标进行分页
		return ViewUtil.getJsonView(docs);
	}
	
}
